package model;

import java.sql.*;
import java.util.*;

public class MessageDAO {

	/**
	 * 단문메세지 보내기
	 * <br>
	 * Message 정보 필요
	 * */
	public int sendMessage(Message message){
		
		int result = 0;
		Connection conn = null;
		
		
		//String sql = "insert into message values( null , "a","b","testmesg",now(),1)";
		String sql = "insert into message values( null , ?,?,?,now(),0)"; 
		
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			//메세지 세팅
			pstmt.setString(1, message.getSendId());	//보낸이
			pstmt.setString(2, message.getReceiveId());	//받는이
			pstmt.setString(3, message.getMessage());	//메세지
			
			result = pstmt.executeUpdate();			
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return result;
	}

	/**
	 * 단문메세지 삭제
	 * <br>
	 * MessageNo 필요
	 * */
	public int delete(int mno){
		
		int result = 0;
		
		String sql = "DELETE FROM message WHERE mno=?";
		Connection conn = null;
		
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mno);
			result = pstmt.executeUpdate();
	
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 단문메세지 보관
	 * <br>
	 * MessageNo 필요
	 * */
	public int holdMessage(int mno){
		
		int result = 0;
		Connection conn = null;
		String sql = "update message set behold=? where mno=?";
		
		try {
			
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
	
			pstmt.setInt(1, 1); //보관
			pstmt.setInt(2, mno);
				
			result = pstmt.executeUpdate();			
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}		
		return result;		
	}

	
	/**
	 * 해당메세지 건수
	 * <br>
	 * behold = 0 수신
	 * behold = 1 읽음
	 * behold = 2 보관
	 * */
	public int getMessageCount(String mid , int behold){
		
		int result = 0;
		Connection conn = null;
		
		
		String sql = "SELECT COUNT(*) FROM message WHERE receiveid = ? AND behold = ? ";
		
		try {
	
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, mid);
			pstmt.setInt(2,behold);
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				result = rs.getInt(1);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	
	public int getMessageReadCount(String mid){
		
		int result = 0;
		Connection conn = null;
		
		String sql = "SELECT COUNT(*) FROM message WHERE receiveid = ? AND behold between 0 and 1";
		
		try {
	
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, mid);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				result = rs.getInt(1);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}



	/**
	 * 해당메세지 추출(페이징)
	 * <br>
	 * mid=아이디
	 * behold = 0 수신
	 * behold = 1 읽음
	 * behold = 2 보관
	 * */
	public Vector<Message> getMessageListPage( String mid , int behold , long currentPage, long rowsPerPage){
		
		Vector<Message> vector = new Vector<Message>();
		Connection conn = ConnectionManager.getConnection();
		
		String sql = 
			"select m2.sendid , m2.message , Date_format(m2.senddate,'%Y-%m-%d'),m2.behold, m2.mno from		"+ 
			"(																				"+		
			"	select m1.*,(@rownum:=@rownum+1) num1 from									"+
			"	(																			"+
			"		SELECT m.* , (@rownum:=0) num 											"+
			"		FROM message as m														"+
			"		WHERE receiveid='"+mid+"' AND behold="+behold+"							"+
			"		ORDER BY senddate desc													"+
			"	) as m1																		"+
			") as m2 limit "+rowsPerPage*(currentPage-1)+","+rowsPerPage;					
		System.out.println(sql);
		try {
			
			///////////////////////////////////////////////
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				
				Message m = new Message();
				m.setSendId(rs.getString(1));
				m.setMessage(rs.getString(2));
				m.setSendDate(rs.getString(3));
				m.setBehold(rs.getInt(4));
				m.setMno(rs.getInt(5));
				vector.add(m);
			}
			rs.close();
			stmt.close();
			///////////////////////////////////////////////
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) try { conn.close(); } catch(Exception e) {}
		}
		return vector;
	}
	
	
	/**
	 * 해당메세지 추출(페이징)
	 * <br>
	 * mid=아이디
	 * behold = 0 수신
	 * behold = 1 읽음
	 * behold = 2 보관
	 * */
	public Vector<Message> getMessageListPage( String mid , long currentPage, long rowsPerPage){
		
		Vector<Message> vector = new Vector<Message>();
		Connection conn = ConnectionManager.getConnection();
		
		String sql = 
			"select m2.sendid , m2.message , Date_format(m2.senddate,'%Y-%m-%d') ,m2.behold, m2.mno from		"+ 
			"(																							"+		
			"	select m1.*,(@rownum:=@rownum+1) num1 from												"+
			"	(																						"+
			"		SELECT m.* , (@rownum:=0) num 														"+
			"		FROM message as m																	"+
			"		WHERE receiveid='"+mid+"' AND behold between 0 and 1								"+
			"		ORDER BY senddate desc																"+
			"	) as m1																					"+
			") as m2 limit "+rowsPerPage*(currentPage-1)+","+rowsPerPage;					
		
		System.out.println(sql);
		
		
		try {
			
			///////////////////////////////////////////////
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				
				Message m = new Message();
				m.setSendId(rs.getString(1));
				m.setMessage(rs.getString(2));
				m.setSendDate(rs.getString(3));
				m.setBehold(rs.getInt(4));
				m.setMno(rs.getInt(5));
				vector.add(m);
			}
			rs.close();
			stmt.close();
			///////////////////////////////////////////////
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) try { conn.close(); } catch(Exception e) {}
		}
		return vector;
	}
	
	//쪽지 읽기
	public Message getMessage(int mno){
		Connection conn = ConnectionManager.getConnection();
		String sql = "select * from message where mno="+mno;
		Message message = null;
		try{
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			if(rs.next()){
				message = new Message();
				message.setMno(rs.getInt(1));
				message.setSendId(rs.getString(2));
				message.setReceiveId(rs.getString(3));
				message.setMessage(rs.getString(4));
				message.setSendDate(rs.getString(5));
				message.setBehold(rs.getInt(6));
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null){
				try{
					conn.close();
				}catch(Exception e){}
			}
		}
		return message;
	}
	
	//수신확인 및 보관 
	public boolean updateBehold(int mno, int behold){
		Connection conn = ConnectionManager.getConnection();
		String sql1 = "update message set behold=2 where mno="+mno;//보관
		String sql2 = "update message set behold=1 where mno="+mno;//수신확인
		Message message = null;
		try{
			Statement st = conn.createStatement();
			//수신확인일 경우
			if(behold==1){
				st.executeUpdate(sql2);
			//보관할 경우
			}else if(behold==2){
				st.executeUpdate(sql1);
			}
						
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			if(conn!=null){
				try{
					conn.close();
				}catch(Exception e){}
			}
		}		
	}

}
